package cn.com.klun.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelTools {
    private static Logger logger = LoggerFactory.getLogger(ExcelTools.class);

    public ExcelTools() {
    }

    /**
     * 判断导入的文档类型
     * @param inp
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(inp);
        return wb;
    }

    public static List<List<String>> excelToList(String excelFile) throws IOException, InvalidFormatException {
        List<List<String>> data = new ArrayList();
        InputStream is = new FileInputStream(excelFile);
        Workbook wb = create(is);
        Sheet sheet = wb.getSheetAt(0);

        //HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

        for(int rowNum = 1; rowNum <= sheet.getLastRowNum(); ++rowNum) {
            Row row = sheet.getRow(rowNum);
            if(row != null) {
                List<String> tmp = new ArrayList();

                for(int cellNum = 0; cellNum <= row.getLastCellNum(); ++cellNum) {
                    Cell cell = row.getCell(cellNum);
                    if(cell == null) {
                        tmp.add("");
                    } else {
                        tmp.add(cell.toString());
                    }
                }

                data.add(tmp);
            }
        }

        return data;
    }

    public static List<List<String>> excelToList(InputStream is) throws IOException {
        List<List<String>> data = new ArrayList();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

        for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); ++rowNum) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if(hssfRow != null) {
                List<String> tmp = new ArrayList();

                for(int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); ++cellNum) {
                    HSSFCell hssfCell = hssfRow.getCell(cellNum);
                    if(hssfCell == null) {
                        tmp.add("");
                    } else {
                        tmp.add(new String(getValue(hssfCell)));
                    }
                }

                data.add(tmp);
            }
        }

        return data;
    }

    //
    private static String getValue(HSSFCell hssfCell) {
        return hssfCell.getCellType() == 4?String.valueOf(hssfCell.getBooleanCellValue()):(hssfCell.getCellType() == 0?String.valueOf(hssfCell.getNumericCellValue()):String.valueOf(hssfCell.getStringCellValue()));
    }

    //
    public HSSFWorkbook export(List<List<String>> list) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheetx");

        for(int i = 0; i < list.size(); ++i) {
            HSSFRow row = sheet.createRow(i);

            for(int j = 0; j < ((List)list.get(i)).size(); ++j) {
                row.createCell(j).setCellValue((String)((List)list.get(i)).get(j));
            }
        }

        return wb;
    }
}
